﻿using Nova.Data;
using Nova_MOD;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Nova_DAL
{
    public class TCommandDal : BaseRepository<TCommands>
    {
        public DeviceCmdInfoModel GetDeviceCmdInfo()
        {
            string sqlStr = @"SELECT DISTINCT TOP (1)  TSP.SQIDE SQID,COUNT(TCM.TaskID) CmdCount 
                    FROM dbo.TSQLanePath TSP INNER JOIN dbo.TDeviceState TDS
                    ON (TSP.SQIDE = TDS.SQID AND TDS.Enable = 1 AND TDS.ControlMod = 5) INNER JOIN dbo.TDeviceState TDT
                    ON (TSP.MiddleConveryerE = TDT.SQID AND TDT.Enable = 1 AND  TDS.ControlMod = 5)
                    LEFT JOIN dbo.TCommands TCM ON (TSP.SQIDE = TCM.EquipmentE_ID OR TSP.SQIDE = TCM.EquipmentS_ID)
                    GROUP BY TSP.SQIDE ORDER BY CmdCount ASC";
            return GetByCondition<DeviceCmdInfoModel>(sqlStr);
        }

        public List<DeviceCmdInfoModel> GetDeviceCmdList()
        {
            string sqlStr = @"SELECT TSP.SQIDE SQID,COUNT(TCM.TaskID) CmdCount 
                    FROM dbo.TSQLanePath TSP INNER JOIN dbo.TDeviceState TDS
                    ON (TSP.SQIDE = TDS.SQID AND TDS.Enable = 1 AND TDS.ControlMod = 5) INNER JOIN dbo.TDeviceState TDT
                    ON (TSP.MiddleConveryerE = TDT.SQID AND TDT.Enable = 1 AND  TDS.ControlMod = 5)
                    LEFT JOIN dbo.TCommands TCM ON (TSP.SQIDE = TCM.EquipmentE_ID OR TSP.SQIDE = TCM.EquipmentS_ID)
                    GROUP BY TSP.SQIDE ORDER BY CmdCount ASC";
            return GetByConditionList<DeviceCmdInfoModel>(sqlStr);
        }

        public LCDTaskInfoModel GetLcdTaskInfoByTaskId(long taskId)
        {
            string sqlStr = string.Format(@"SELECT TC.TaskID,TC.Tray_ID,TSR.PLCType,TSR.WMSTypeName,ISNULL(TCD.MAT_NUM,0) MAT_NUM, 
                    ISNULL(TDL.StockNo,'') StockNo,ISNULL(TDL.Mat_Code,'') Mat_Code, TBM.S01,TBM.S05,TBM.TM01,
                    ISNULL(TBM.Mat_Name,'') Mat_Name,TDL.TS01,TC.PosS_ID,TC.PosE_ID
                    FROM  dbo.TCommands TC LEFT JOIN  dbo.TCommandsDetail TCD ON TC.TaskID = TCD.TaskID  
                    LEFT JOIN dbo.TDelStorage_List TDL ON TCD.DETAIL_PID = TDL.PID LEFT JOIN dbo.TBase_Material TBM
                    ON TDL.Mat_Code = TBM.Mat_Code LEFT JOIN dbo.TSQModeRelations TSR ON TC.TaskMode = TSR.WMSType
                    WHERE TC.TaskID ={0}", taskId);
            return GetByCondition<LCDTaskInfoModel>(sqlStr);
        }

        public LCDTaskInfoModel GetLcdReplenTaskInfoByTaskId(long taskId)
        {
            string sqlStr = string.Format(@"SELECT TC.TaskID,TC.Tray_ID,TSR.PLCType,TSR.WMSTypeName,ISNULL(TDL.Num - TDL.Act_Num,0) MAT_NUM, 
                    ISNULL(TDL.StockNo,'') StockNo,ISNULL(TDL.Mat_Code,'') Mat_Code,TBM.S01,TBM.S05,TBM.TM01, 
                    ISNULL(TBM.Mat_Name,'') Mat_Name,TDL.TS01,TC.PosS_ID,TC.PosE_ID
                    FROM  dbo.TCommands TC LEFT JOIN  dbo.TCommandsDetail TCD ON TC.TaskID = TCD.TaskID  
                    LEFT JOIN dbo.TAddStorage_List TDL ON TCD.DETAIL_PID = TDL.PID LEFT JOIN dbo.TBase_Material TBM
                    ON TDL.Mat_Code = TBM.Mat_Code LEFT JOIN dbo.TSQModeRelations TSR ON TC.TaskMode = TSR.WMSType
                    WHERE TC.TaskID ={0}", taskId);
            return GetByCondition<LCDTaskInfoModel>(sqlStr);
        }

        public bool IsDeviceEnable(int SQID)
        {
            string sqlStr = string.Format(@"SELECT  COUNT(TDS.SQID) FROM dbo.TSQLanePath TSP INNER JOIN dbo.TDeviceState TDS
                    ON (TSP.SQIDE = TDS.SQID AND TDS.Enable = 1 AND TDS.ControlMod = 5) INNER JOIN dbo.TDeviceState TDT
                    ON (TSP.MiddleConveryerE = TDT.SQID AND TDT.Enable = 1 AND TDT.ControlMod = 5)
	                INNER JOIN  dbo.TDeviceState TST  ON TSP.NO_Lane = TST.SQID AND TST.Enable = 1 AND TST.ControlMod = 5
                    WHERE TSP.SQIDE  = {0}", SQID);
            object obj = ExecuteScalar(sqlStr);
            if (obj != null)
            {                                             
                return int.Parse(obj.ToString()) > 0;
            }
            else
                return false;
        }

        public List<PosPrimitiveTypeModel> GetPosPrimitiveTypeList(int abc_Kind, int typeOfSize)
        {
            string sqlStr = string.Format(@"SELECT (No_Layer%2) PosPrimitType,COUNT(Pos_ID) PosCount   
                        FROM dbo.TBase_Position  WHERE ABC_Kind = {0}  AND Pos_State =0 AND TypeOfSize >= {1} 
                        GROUP BY (No_Layer%2)", abc_Kind, typeOfSize);
            return GetByConditionList<PosPrimitiveTypeModel>(sqlStr);
        }

        public List<TCommands> GetListByEquimentE()
        {
            string sqlStr = string.Format(@"SELECT TCM.TaskID,TCM.EquipmentE_ID,TSQ.PLCType AS TaskMode FROM dbo.TCommands TCM 
                    INNER JOIN dbo.TSQModeRelations TSQ ON TCM.TaskMode = TSQ.WMSType");
            return GetByConditionList(sqlStr);
        }
    }
}
